﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL.DataOperate
{
    public class CommentDAL:DAL.App_Code.DBBase
    {
        public CommentDAL() :
            base() { }

        public DataSet SelectAll()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select Comm_Idint,tbComment_Infor.Book_Idint,Book_Namestr,tbComment_Infor.User_Idint,User_Namestr,Comm_PublishDatedate ");
            sqlStr.Append("from tbComment_Infor join tbBook_Infor on tbComment_Infor.Book_Idint=tbBook_Infor.Book_Idint ");
            sqlStr.Append(" join tbUser_Infor on tbComment_Infor.User_Idint = tbUser_Infor.User_Idint ");

            SqlParameter[] pa = { };
            return ExecuteString(sqlStr.ToString(), pa, "tbComment_Infor");
        }
        public DataSet SelectByCommentId(int CommentId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select Comm_Detailtext ");
            sqlStr.Append("from tbComment_Infor ");
            sqlStr.Append("where Comm_Idint = @Comm_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@Comm_Idint",CommentId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbComment_Infor");
        }
        public DataSet SelectByBookId(int BookId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select User_Namestr,Comm_Detailtext,Comm_PublishDatedate ");
            sqlStr.Append("from tbComment_Infor join tbUser_Infor on tbComment_Infor.User_Idint = tbUser_Infor.User_Idint ");
            sqlStr.Append("and Book_Idint = @Book_Idint ");

            SqlParameter[] pa = {
                new SqlParameter("@Book_Idint",BookId)
                                };
            return ExecuteString(sqlStr.ToString(), pa, "tbComment_Infor");
        }
        public DataSet SelectByBookUserId(int BookId, int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select Comm_Idint from vi_Comment ");
            sqlStr.Append("where Book_Idint = @Book_Idint ");
            sqlStr.Append("and User_Idint = @User_Idint");
            
            SqlParameter[] pa = {
                new SqlParameter ("@Book_Idint",BookId),
                new SqlParameter ("@User_Idint",UserId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbComment_Infor");
        }
        public bool Insert(Model.Comment comment)
        {
            int affectedRow = 0;
            int returnValue = 0;
            SqlParameter[] pa = {
                new SqlParameter("@Book_Idint",comment.Book_Idint),
                new SqlParameter("@Comm_Captionstr",comment.Comm_Captionstr),
                new SqlParameter("@Comm_Detailtext",comment.Comm_Detailtext),
                new SqlParameter("@Comm_PublishDatedate",comment.Comm_PublishDatedate),
                new SqlParameter("@Comm_Rankint",comment.Comm_Rankint),
                new SqlParameter("@User_Idint",comment.User_Idint)
                                };

             returnValue = ExecuteRetProcedure("sp_InsertComment", pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool Delete(int CommentId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            sqlStr.Append("delete from tbComment_Infor ");
            sqlStr.Append("where Comm_Idint = @Comm_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@Comm_Idint",CommentId)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;

        }
    }
}
